﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using MREO.data;
using System.Data.SqlServerCe;

namespace MREO.gui
{
    public partial class fTemplates_AddEdit : Form
    {
        public fMain mainForm;
        public fTemplates templForm;

        int templId = 0;

        Connection Conn = new Connection();
        SqlCeDataAdapter adapter = new SqlCeDataAdapter();
        SqlCeCommand command = null;

        SqlCeTransaction tx;
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pObjTempl">Object[] { dr["id"], dr["doc_type"], dr["doc_name"], dr["template"], dr["groupDoc"] }</param>
        public fTemplates_AddEdit(Object[] pObjTempl)
        {
            
            InitializeComponent();

            templId = int.Parse(pObjTempl[0].ToString());
            comboBox_docType.SelectedIndex = int.Parse(pObjTempl[1].ToString());
            comboBox_docgroup.SelectedIndex = int.Parse(pObjTempl[4].ToString());
            textBox_docName.Text = pObjTempl[2].ToString();
            if(pObjTempl[3].ToString().Length > 0)
                webBrowser_template.DocumentText = Encoding.UTF8.GetString((byte[])pObjTempl[3]);

            // Получаем параметры
            getTemplateParam(templId);

            // если есть шаблон делаем активную кнопку 'Выгрузить'
            if (pObjTempl[3].ToString().Length > 0) button_get.Enabled = true;
        }

        public fTemplates_AddEdit()
        {

            InitializeComponent();

        }

        // Получаем перечень параметров для шаблона
        private void getTemplateParam(int templId)
        {
            DataTable dt = new DataTable();

            try
            {
                Conn.con.Open();

                command = new SqlCeCommand("SELECT mnuItems, xname, xvalue " +
                                            " FROM templateParam WHERE mnuItems = @templId ORDER BY xname"
                                            , Conn.con);
                command.Parameters.Add("@templId", SqlDbType.Int).Value = templId;
                adapter.SelectCommand = command;
                adapter.Fill(dt);

            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
                return;
            }
            finally
            {
                command.Dispose();
                adapter.Dispose();
                if (Conn.con.State == ConnectionState.Open)
                    Conn.con.Close();
            }

            dataGridView_templ.AutoGenerateColumns = false;
            dataGridView_templ.DataSource = dt;
            dataGridView_templ.Columns["mnuItems"].DataPropertyName = "mnuItems";
            dataGridView_templ.Columns["xname"].DataPropertyName = "xname";
            dataGridView_templ.Columns["xvalue"].DataPropertyName = "xvalue";

        }

        // загружаем шаблон
        private void button_set_Click(object sender, EventArgs e)
        {
            byte[] tempBody;
            OpenFileDialog ofd = new OpenFileDialog();
            if (ofd.ShowDialog(this) != DialogResult.OK) return;

            Stream str = ofd.OpenFile();
            tempBody = new byte[str.Length];
            str.Read(tempBody, 0, (int)str.Length);

            webBrowser_template.DocumentText = Encoding.UTF8.GetString(tempBody);

        }

        // Применяем параметры к шаблону
        private void button_applyParam_Click(object sender, EventArgs e)
        {
            string xName;
            string xValue;

            for (int i = 0; i < dataGridView_templ.Rows.Count - 1; i++)
            {
                xName = dataGridView_templ.Rows[i].Cells["xvalue"].Value.ToString();

                if (xName.Substring(0, 1).Equals("#")) // Глобальный параметр
                {
                    xName = xName.Substring(1, xName.Length - 1);
                    xValue = getGValParamValue(xName);
                }
                else
                {
                    xValue = xName;
                }

                webBrowser_template.Document.InvokeScript(dataGridView_templ.Rows[i].Cells["xname"].Value.ToString(), new object[] { xValue });
            }
        }

        private string getGValParamValue(string xName)
        {
            string xvalue = xName; // Если не найдется параметр вернется имч указанного параметра
            DataTable dt = new DataTable();

            try
            {
                Conn.con.Open();

                command = new SqlCeCommand("SELECT xvalue " +
                                            " FROM gval WHERE xname = @xname"
                                            , Conn.con);
                command.Parameters.Add("@xname", SqlDbType.NVarChar).Value = xName;
                adapter.SelectCommand = command;
                adapter.Fill(dt);
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
            finally
            {
                command.Dispose();
                adapter.Dispose();
                if (Conn.con.State == ConnectionState.Open)
                    Conn.con.Close();
            }

            foreach (DataRow dr in dt.AsEnumerable())
            {
                xvalue = dr["xvalue"].ToString();
            }

            return xvalue;
        }

        private void button_save_Click(object sender, EventArgs e)
        {
            string msgError = "Не заданы след обязательные данные:";
            
            int doc_type;
            int doc_group;
            string doc_name = string.Empty;
            string template = string.Empty;
            DataTable dtParam = new DataTable();

            doc_type = comboBox_docType.SelectedIndex;
            doc_group = comboBox_docgroup.SelectedIndex;
            doc_name = textBox_docName.Text.Trim();
            template = webBrowser_template.DocumentText;

            if (doc_type == -1) msgError += "\nТип документа;";
            if (doc_name.Length == 0) msgError += "\nНаименование документа;";
            if (template.Length == 0) msgError += "\nШаблон документа;";

            if (msgError != "Не заданы след обязательные данные:")
            {
                MessageBox.Show(msgError);
                return;
            }

            if (templId == 0)
            {
                // Сохраняем шаблон
                try
                {
                    saveTemplate(doc_type, doc_name, template, doc_group);
                }
                catch (Exception exc)
                {
                    MessageBox.Show("Не удалось сохранить шаблон. \n\n" + exc.Message);
                    return;
                }
            }
            else
            {
                // Редактируем шаблон
                try
                {
                    editTemplate(doc_type, doc_name, template, doc_group);
                }
                catch (Exception exc)
                {
                    MessageBox.Show("Не удалось отредактировать шаблон. \n\n" + exc.Message);
                    return;
                }
            }

            dtParam = new DataTable();

            //перечитываем меню
            supportMethod supMethod = new supportMethod();
            dtParam = supMethod.loadMnu();

            //Обновляем меню
            mainForm.updateMNU(dtParam);

            MessageBox.Show("Шаблон сохранен");
            Dispose();
            templForm.updateTree(dtParam);
        }

        // Редактируем шаблон
        private void editTemplate(int doc_type, string doc_name, string template, int pDoc_group)
        {
            byte[] bTemplate = Encoding.UTF8.GetBytes(template);
            try
            {
                Conn.con.Open();

                tx = Conn.con.BeginTransaction();

                command = new SqlCeCommand("UPDATE mnuItems SET doc_type = @doc_type, doc_name = @doc_name, template = @template, groupDoc = @groupDoc" +
                                            " WHERE id = @templId"
                                            , Conn.con);

                command.Transaction = tx;

                command.Parameters.Add("@doc_type", SqlDbType.Int).Value = doc_type;
                command.Parameters.Add("@groupDoc", SqlDbType.Int).Value = pDoc_group;
                command.Parameters.Add("@doc_name", SqlDbType.NVarChar).Value = doc_name;
                command.Parameters.Add("@template", SqlDbType.Image, bTemplate.Length).Value = bTemplate;
                command.Parameters.Add("@templId", SqlDbType.Int).Value = templId;
                command.ExecuteNonQuery();

                command.Parameters.Clear();

                command.CommandText = "DELETE FROM templateParam WHERE mnuItems = @templId";
                command.Parameters.Add("@templId", SqlDbType.Int).Value = templId;
                command.ExecuteNonQuery();

                command.Parameters.Clear();

                if (dataGridView_templ.Rows.Count > 0)    // Если есть параметры сохраняем их
                {
                    command.CommandText = "INSERT INTO templateParam(mnuItems, xname, xvalue) VALUES(@mnuItems, @xname, @xvalue)";

                    foreach (DataGridViewRow dr in dataGridView_templ.Rows)
                    {
                        if (dr.Cells["xname"].Value == null) continue;

                        command.Parameters.Clear();

                        command.Parameters.Add("@mnuItems", SqlDbType.Int).Value = templId;
                        command.Parameters.Add("@xname", SqlDbType.NVarChar).Value = dr.Cells["xname"].Value.ToString();
                        command.Parameters.Add("@xvalue", SqlDbType.NVarChar).Value = dr.Cells["xvalue"].Value.ToString();

                        command.ExecuteNonQuery();
                    }
                }

                tx.Commit();

            }
            catch (Exception exc)
            {
                tx.Rollback();
                throw new Exception(exc.Message);
            }
            finally
            {
                command.Dispose();
                adapter.Dispose();
                if (Conn.con.State == ConnectionState.Open)
                    Conn.con.Close();
            }
        }

        // Сохраняем шаблон
        private void saveTemplate(int pDocType, string pDocName, string pTemplate, int pDoc_group)
        {
            int NewID;

            byte[] bTemplate = Encoding.UTF8.GetBytes(pTemplate);

            try
            {
                Conn.con.Open();

                tx = Conn.con.BeginTransaction();

                command = new SqlCeCommand("INSERT INTO mnuItems(doc_type, doc_name, template, groupDoc) VALUES(@doc_type, @doc_name, @template, @groupDoc)"
                                            , Conn.con);
                command.Transaction = tx;

                command.Parameters.Add("@doc_type", SqlDbType.Int).Value = pDocType;
                command.Parameters.Add("@groupDoc", SqlDbType.Int).Value = pDoc_group;
                command.Parameters.Add("@doc_name", SqlDbType.NVarChar).Value = pDocName;
                command.Parameters.Add("@template", SqlDbType.Image, bTemplate.Length).Value = bTemplate;

                command.ExecuteNonQuery();

                command.CommandText = "SELECT @@IDENTITY";
                command.Parameters.Clear();
                NewID = int.Parse(command.ExecuteScalar().ToString());

                
                if (dataGridView_templ.Rows.Count > 0)    // Если есть параметры сохраняем их
                {
                    command.CommandText = "INSERT INTO templateParam(mnuItems, xname, xvalue) VALUES(@mnuItems, @xname, @xvalue)";
                    foreach (DataGridViewRow dr in dataGridView_templ.Rows)
                    {
                        if (dr.Cells["xname"].Value == null) continue;

                        command.Parameters.Clear();

                        command.Parameters.Add("@mnuItems", SqlDbType.Int).Value = NewID;
                        command.Parameters.Add("@xname", SqlDbType.NVarChar).Value = dr.Cells["xname"].Value.ToString();
                        command.Parameters.Add("@xvalue", SqlDbType.NVarChar).Value = dr.Cells["xvalue"].Value.ToString();

                        command.ExecuteNonQuery();
                    }
                }

                tx.Commit();

            }
            catch (Exception exc)
            {
                tx.Rollback();
                throw new Exception(exc.Message);
            }
            finally
            {
                command.Dispose();
                adapter.Dispose();
                if (Conn.con.State == ConnectionState.Open)
                    Conn.con.Close();
            }
        }

        private void button_get_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            if (sfd.ShowDialog() != DialogResult.OK) return;

            try
            {
                using (StreamWriter sw = new StreamWriter(File.Open(sfd.FileName, FileMode.OpenOrCreate), Encoding.UTF8))
                {
                    sw.WriteLine(webBrowser_template.DocumentText);
                }
            }
            catch (Exception exc) { MessageBox.Show(exc.Message); return;}

            MessageBox.Show("Файл шаблона сформирован");
        }
    }
}
